using System;
using System.Data;
using System.Data.OleDb;
using System.Collections;
using System.Configuration;
using System.Runtime.InteropServices;

//Using PCS's namespaces 
using PCSComUtils.DataAccess;
using PCSComUtils.PCSExc;
using PCSComUtils.Common;

namespace PCSComProduction.ScrapRecording.DS
{
	public class PRO_AOScrapDetailDS //: PCSComUtils.Generic.IObjectDS 
	{
		#region Declaration, Constructor
		
		public const string AVAILABLE_QUANTITY_FIELD = "AvailableQuantity";
		public const string UNIT_COST_FIELD = "UnitCost";
		public const string SCRAP_COST_FIELD = "ScrapCost";		

		private const string THIS = "PCSComProduction.ScrapRecording.DS.PRO_AOScrapDetailDS";
		
		public PRO_AOScrapDetailDS()
		{
		}		
		
		#endregion Declaration, Constructor

		#region Update Data Methods

		///    <summary>
		///       This method uses to add data to PRO_AOScrapDetail
		///    </summary>
		///    <Inputs>
		///        PRO_AOScrapDetailVO       
		///    </Inputs>
		///    <Returns>
		///       void
		///    </Returns>
		///    <History>
		///       Thursday, June 09, 2005
		///    </History>
		public void Add(object pobjObjectVO)
		{
			const string METHOD_NAME = THIS + ".Add()";
			 
			OleDbConnection oconPCS = null;
			OleDbCommand ocmdPCS = null;
			try
			{
				PRO_AOScrapDetailVO objObject = (PRO_AOScrapDetailVO) pobjObjectVO;				
				
				oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);				
				
				string strSql = "INSERT INTO PRO_AOScrapDetail("
					+ PRO_AOScrapDetailTable.SCRAPQUANTITY_FLD + ", "
					+ PRO_AOScrapDetailTable.WORKORDERDETAILID_FLD + ", "
					+ PRO_AOScrapDetailTable.PRODUCTID_FLD + ", "
					+ PRO_AOScrapDetailTable.WORKORDERMASTERID_FLD + ", "
					+ PRO_AOScrapDetailTable.LINE_FLD + ", "
					+ PRO_AOScrapDetailTable.SCRAPREASONID_FLD + ", "
					+ PRO_AOScrapDetailTable.WOROUTINGID_FLD + ", "
					+ PRO_AOScrapDetailTable.STOCKUMID_FLD + ", "
					+ PRO_AOScrapDetailTable.AOSCRAPMASTERID_FLD + ")"
					+ "VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?)";
				
				ocmdPCS = new OleDbCommand(strSql, oconPCS);

				ocmdPCS.Parameters.Add(new OleDbParameter(PRO_AOScrapDetailTable.SCRAPQUANTITY_FLD, OleDbType.Decimal));
				ocmdPCS.Parameters[PRO_AOScrapDetailTable.SCRAPQUANTITY_FLD].Value = objObject.ScrapQuantity;

				ocmdPCS.Parameters.Add(new OleDbParameter(PRO_AOScrapDetailTable.WORKORDERDETAILID_FLD, OleDbType.Integer));
				ocmdPCS.Parameters[PRO_AOScrapDetailTable.WORKORDERDETAILID_FLD].Value = objObject.WorkOrderDetailID;

				ocmdPCS.Parameters.Add(new OleDbParameter(PRO_AOScrapDetailTable.PRODUCTID_FLD, OleDbType.Integer));
				ocmdPCS.Parameters[PRO_AOScrapDetailTable.PRODUCTID_FLD].Value = objObject.ProductID;

				ocmdPCS.Parameters.Add(new OleDbParameter(PRO_AOScrapDetailTable.WORKORDERMASTERID_FLD, OleDbType.Integer));
				ocmdPCS.Parameters[PRO_AOScrapDetailTable.WORKORDERMASTERID_FLD].Value = objObject.WorkOrderMasterID;

				ocmdPCS.Parameters.Add(new OleDbParameter(PRO_AOScrapDetailTable.LINE_FLD, OleDbType.Integer));
				ocmdPCS.Parameters[PRO_AOScrapDetailTable.LINE_FLD].Value = objObject.Line;

				ocmdPCS.Parameters.Add(new OleDbParameter(PRO_AOScrapDetailTable.SCRAPREASONID_FLD, OleDbType.Integer));
				ocmdPCS.Parameters[PRO_AOScrapDetailTable.SCRAPREASONID_FLD].Value = objObject.ScrapReasonID;

				ocmdPCS.Parameters.Add(new OleDbParameter(PRO_AOScrapDetailTable.WOROUTINGID_FLD, OleDbType.Integer));
				ocmdPCS.Parameters[PRO_AOScrapDetailTable.WOROUTINGID_FLD].Value = objObject.WORoutingID;
				
				ocmdPCS.Parameters.Add(new OleDbParameter(PRO_AOScrapDetailTable.STOCKUMID_FLD, OleDbType.Integer));
				ocmdPCS.Parameters[PRO_AOScrapDetailTable.STOCKUMID_FLD].Value = objObject.StockUMID;

				ocmdPCS.Parameters.Add(new OleDbParameter(PRO_AOScrapDetailTable.AOSCRAPMASTERID_FLD, OleDbType.Integer));
				ocmdPCS.Parameters[PRO_AOScrapDetailTable.AOSCRAPMASTERID_FLD].Value = objObject.AOScrapMasterID;
				
				ocmdPCS.Connection.Open();
				ocmdPCS.ExecuteNonQuery();
			}
			catch(OleDbException ex)
			{
				if (ex.Errors[1].NativeError == ErrorCode.SQLDUPLICATE_KEYCODE)
				{																   
					throw new PCSDBException(ErrorCode.DUPLICATE_KEY, METHOD_NAME, ex);				
				}
				else
				{
					throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME,ex);
				}
			}
			catch(InvalidOperationException ex)
			{
				throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME,ex);
			}
			catch (Exception ex) 
			{
				throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
			}
			finally 
			{
				if (oconPCS!=null) 
				{
					if (oconPCS.State != ConnectionState.Closed) 
					{
						oconPCS.Close();
					}
				}
			}
		}	
		
		
		///    <summary>
		///       This method uses to update data of PRO_AOScrapDetail
		///    </summary>
		///    <Inputs>
		///        PRO_AOScrapDetailVO       
		///    </Inputs>
		///    <Returns>
		///       void
		///    </Returns>
		///    <History>
		///       Thursday, June 09, 2005
		///    </History>
		public void Update(object pobjObjecVO)
		{
			const string METHOD_NAME = THIS + ".Update()";

			PRO_AOScrapDetailVO objObject = (PRO_AOScrapDetailVO) pobjObjecVO;
			
			//prepare value for parameters
			OleDbConnection oconPCS = null;
			OleDbCommand ocmdPCS = null;
			try 
			{
				
				
				oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
				
				string strSql =	"UPDATE PRO_AOScrapDetail SET "
					+ PRO_AOScrapDetailTable.SCRAPQUANTITY_FLD + "= ? ,"
					+ PRO_AOScrapDetailTable.WORKORDERDETAILID_FLD + "= ? ,"
					+ PRO_AOScrapDetailTable.PRODUCTID_FLD + "= ?, "
					+ PRO_AOScrapDetailTable.WORKORDERMASTERID_FLD + "= ?, "
					+ PRO_AOScrapDetailTable.LINE_FLD + "= ?, "
					+ PRO_AOScrapDetailTable.SCRAPREASONID_FLD + "= ?, "
					+ PRO_AOScrapDetailTable.WOROUTINGID_FLD + "= ?, "
					+ PRO_AOScrapDetailTable.STOCKUMID_FLD + "=?, "
					+ PRO_AOScrapDetailTable.AOSCRAPMASTERID_FLD + "=? "					
					+ " WHERE " + PRO_AOScrapDetailTable.AOSCRAPDETAILID_FLD + "=?";
				
				ocmdPCS = new OleDbCommand(strSql, oconPCS);
				ocmdPCS.Parameters.Add(new OleDbParameter(PRO_AOScrapDetailTable.SCRAPQUANTITY_FLD, OleDbType.Decimal));
				ocmdPCS.Parameters[PRO_AOScrapDetailTable.SCRAPQUANTITY_FLD].Value = objObject.ScrapQuantity;

				ocmdPCS.Parameters.Add(new OleDbParameter(PRO_AOScrapDetailTable.WORKORDERDETAILID_FLD, OleDbType.Integer));
				ocmdPCS.Parameters[PRO_AOScrapDetailTable.WORKORDERDETAILID_FLD].Value = objObject.WorkOrderDetailID;

				ocmdPCS.Parameters.Add(new OleDbParameter(PRO_AOScrapDetailTable.PRODUCTID_FLD, OleDbType.Integer));
				ocmdPCS.Parameters[PRO_AOScrapDetailTable.PRODUCTID_FLD].Value = objObject.ProductID;

				ocmdPCS.Parameters.Add(new OleDbParameter(PRO_AOScrapDetailTable.WORKORDERMASTERID_FLD, OleDbType.Integer));
				ocmdPCS.Parameters[PRO_AOScrapDetailTable.WORKORDERMASTERID_FLD].Value = objObject.WorkOrderMasterID;

				ocmdPCS.Parameters.Add(new OleDbParameter(PRO_AOScrapDetailTable.LINE_FLD, OleDbType.Integer));
				ocmdPCS.Parameters[PRO_AOScrapDetailTable.LINE_FLD].Value = objObject.Line;

				ocmdPCS.Parameters.Add(new OleDbParameter(PRO_AOScrapDetailTable.SCRAPREASONID_FLD, OleDbType.Integer));
				ocmdPCS.Parameters[PRO_AOScrapDetailTable.SCRAPREASONID_FLD].Value = objObject.ScrapReasonID;

				ocmdPCS.Parameters.Add(new OleDbParameter(PRO_AOScrapDetailTable.WOROUTINGID_FLD, OleDbType.Integer));
				ocmdPCS.Parameters[PRO_AOScrapDetailTable.WOROUTINGID_FLD].Value = objObject.WORoutingID;

				ocmdPCS.Parameters.Add(new OleDbParameter(PRO_AOScrapDetailTable.STOCKUMID_FLD, OleDbType.Integer));
				ocmdPCS.Parameters[PRO_AOScrapDetailTable.STOCKUMID_FLD].Value = objObject.StockUMID;

				ocmdPCS.Parameters.Add(new OleDbParameter(PRO_AOScrapDetailTable.AOSCRAPMASTERID_FLD, OleDbType.Integer));
				ocmdPCS.Parameters[PRO_AOScrapDetailTable.AOSCRAPMASTERID_FLD].Value = objObject.AOScrapMasterID;

				ocmdPCS.Parameters.Add(new OleDbParameter(PRO_AOScrapDetailTable.AOSCRAPDETAILID_FLD, OleDbType.Integer));
				ocmdPCS.Parameters[PRO_AOScrapDetailTable.AOSCRAPDETAILID_FLD].Value = objObject.AOScrapDetailID;

				ocmdPCS.Connection.Open();
				ocmdPCS.ExecuteNonQuery();	
			}
			catch(OleDbException ex)
			{
				if (ex.Errors[1].NativeError == ErrorCode.SQLDUPLICATE_KEYCODE)
				{																   
					throw new PCSDBException(ErrorCode.DUPLICATE_KEY, METHOD_NAME, ex);				
				}
				else
				{
					throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME,ex);
				}
			}			

			catch(InvalidOperationException ex)
			{
				throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME,ex);
			}
			catch (Exception ex) 
			{
				throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
			}
			finally 
			{
				if (oconPCS!=null) 
				{
					if (oconPCS.State != ConnectionState.Closed) 
					{
						oconPCS.Close();
					}
				}
			}
		}

		
		///    <summary>
		///       This method uses to detele data from PRO_AOScrapDetail
		///    </summary>
		///    <Inputs>
		///        Specific PRO_AOScrapDetail ID
		///    </Inputs>
		///    <Returns>
		///       void
		///    </Returns>
		///    <History>
		///       Thursday, June 09, 2005
		///    </History>
		public void Delete(int pintID)
		{
			const string METHOD_NAME = THIS + ".Delete()";

			string strSql = "DELETE " + PRO_AOScrapDetailTable.TABLE_NAME + " WHERE  " + PRO_AOScrapDetailTable.AOSCRAPDETAILID_FLD + "=" + pintID.ToString();

			OleDbConnection oconPCS = null;
			OleDbCommand ocmdPCS = null;
			try
			{
				
				oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
				ocmdPCS = new OleDbCommand(strSql, oconPCS);

				ocmdPCS.Connection.Open();
				ocmdPCS.ExecuteNonQuery();
				ocmdPCS = null;
			}
			catch(OleDbException ex)
			{
				if (ex.Errors[1].NativeError == ErrorCode.SQLCASCADE_PREVENT_KEYCODE)
				{																   
					throw new PCSDBException(ErrorCode.CASCADE_DELETE_PREVENT, METHOD_NAME, ex);				
				}
				else
				{
					throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME,ex);
				}
			}
			catch (Exception ex) 
			{
				throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
			}
			finally 
			{
				if (oconPCS!=null) 
				{
					if (oconPCS.State != ConnectionState.Closed) 
					{
						oconPCS.Close();
					}
				}
			}
		}		
				
		///    <summary>
		///       This method uses to update batch AOScrapDetail to PRO_AOScrapDetail table
		///    </summary>
		///    <Inputs>
		///        PRO_AOScrapDetailVO       
		///    </Inputs>
		///    <Returns>
		///       void
		///    </Returns>
		///    <History>
		///       Thursday, June 09, 2005
		///    </History>
		public void UpdateDataSet(DataSet pData)
		{
			const string METHOD_NAME = THIS + ".UpdateDataSet()";
			
			OleDbConnection oconPCS = null;
			OleDbCommandBuilder odcbPCS ;
			OleDbDataAdapter odadPCS = new OleDbDataAdapter();

			try
			{
				string strSql =	"SELECT "
					+ PRO_AOScrapDetailTable.AOSCRAPDETAILID_FLD + ", "
					+ PRO_AOScrapDetailTable.SCRAPQUANTITY_FLD + ", "
					+ PRO_AOScrapDetailTable.WORKORDERDETAILID_FLD + ", "
					+ PRO_AOScrapDetailTable.PRODUCTID_FLD + ", "
					+ PRO_AOScrapDetailTable.WORKORDERMASTERID_FLD + ", "
					+ PRO_AOScrapDetailTable.LINE_FLD + ", "
					+ PRO_AOScrapDetailTable.SCRAPREASONID_FLD + ", "
					+ PRO_AOScrapDetailTable.WOROUTINGID_FLD + ", "
					+ PRO_AOScrapDetailTable.STOCKUMID_FLD + ", "
					+ PRO_AOScrapDetailTable.AOSCRAPMASTERID_FLD
					+ "  FROM " + PRO_AOScrapDetailTable.TABLE_NAME;

				
				oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
				odadPCS.SelectCommand = new OleDbCommand(strSql, oconPCS);
				odcbPCS = new OleDbCommandBuilder(odadPCS);
				pData.EnforceConstraints = false;
				odadPCS.Update(pData, PRO_AOScrapDetailTable.TABLE_NAME);
			}
			catch(OleDbException ex)
			{
				if (ex.Errors[1].NativeError == ErrorCode.SQLDUPLICATE_KEYCODE)
				{																   
					throw new PCSDBException(ErrorCode.DUPLICATE_KEY, METHOD_NAME, ex);				
				}
				else if (ex.Errors[1].NativeError == ErrorCode.SQLCASCADE_PREVENT_KEYCODE)
				{
					throw new PCSDBException(ErrorCode.CASCADE_DELETE_PREVENT, METHOD_NAME, ex);				
				}
				else
				{
					throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME,ex);
				}
			}
			catch(InvalidOperationException ex)
			{
				throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME,ex);
			}
			catch (Exception ex) 
			{
				throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
			}
			finally 
			{
				if (oconPCS!=null) 
				{
					if (oconPCS.State != ConnectionState.Closed) 
					{
						oconPCS.Close();
					}
				}
			}
		}

		#endregion Update Data Methods

		#region Retrieve Data Methods

		///    <summary>
		///       This method uses to get data of specific AOScrapDetail from PRO_AOScrapDetail
		///    </summary>
		///    <Inputs>
		///        detail id : int     
		///    </Inputs>
		///    <Returns>
		///       AOScrapDetailVO object
		///    </Returns>
		///    <History>
		///       Thursday, June 09, 2005
		///    </History>

		public object GetObjectVO(int pintID)
		{
			const string METHOD_NAME = THIS + ".GetObjectVO()";
			DataSet dstPCS = new DataSet();
			
			OleDbDataReader odrPCS = null;
			OleDbConnection oconPCS = null;
			OleDbCommand ocmdPCS = null;
			try 
			{
				string strSql = "SELECT "
					+ PRO_AOScrapDetailTable.AOSCRAPDETAILID_FLD + ", "
					+ PRO_AOScrapDetailTable.SCRAPQUANTITY_FLD + ", "
					+ PRO_AOScrapDetailTable.WORKORDERDETAILID_FLD + ", "
					+ PRO_AOScrapDetailTable.PRODUCTID_FLD + ", "
					+ PRO_AOScrapDetailTable.WORKORDERMASTERID_FLD + ", "
					+ PRO_AOScrapDetailTable.LINE_FLD + ", "
					+ PRO_AOScrapDetailTable.SCRAPREASONID_FLD + ", "
					+ PRO_AOScrapDetailTable.WOROUTINGID_FLD + ", "
					+ PRO_AOScrapDetailTable.STOCKUMID_FLD + ", "
					+ PRO_AOScrapDetailTable.AOSCRAPMASTERID_FLD
					+ " FROM " + PRO_AOScrapDetailTable.TABLE_NAME
					+ " WHERE " + PRO_AOScrapDetailTable.AOSCRAPDETAILID_FLD + "=" + pintID;

				
				oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
				ocmdPCS = new OleDbCommand(strSql, oconPCS);

				ocmdPCS.Connection.Open();
				odrPCS = ocmdPCS.ExecuteReader();

				PRO_AOScrapDetailVO objObject = new PRO_AOScrapDetailVO();

				while (odrPCS.Read())
				{ 
					objObject.AOScrapDetailID = int.Parse(odrPCS[PRO_AOScrapDetailTable.AOSCRAPDETAILID_FLD].ToString().Trim());
					objObject.ScrapQuantity = decimal.Parse(odrPCS[PRO_AOScrapDetailTable.SCRAPQUANTITY_FLD].ToString().Trim());
					objObject.WorkOrderDetailID = int.Parse(odrPCS[PRO_AOScrapDetailTable.WORKORDERDETAILID_FLD].ToString().Trim());
					objObject.ProductID = int.Parse(odrPCS[PRO_AOScrapDetailTable.PRODUCTID_FLD].ToString().Trim());
					objObject.WorkOrderMasterID = int.Parse(odrPCS[PRO_AOScrapDetailTable.WORKORDERMASTERID_FLD].ToString().Trim());
					objObject.Line = int.Parse(odrPCS[PRO_AOScrapDetailTable.LINE_FLD].ToString().Trim());
					objObject.ScrapReasonID = int.Parse(odrPCS[PRO_AOScrapDetailTable.SCRAPREASONID_FLD].ToString().Trim());
					objObject.WORoutingID = int.Parse(odrPCS[PRO_AOScrapDetailTable.WOROUTINGID_FLD].ToString().Trim());
					objObject.StockUMID = int.Parse(odrPCS[PRO_AOScrapDetailTable.STOCKUMID_FLD].ToString().Trim());
					objObject.AOScrapMasterID = int.Parse(odrPCS[PRO_AOScrapDetailTable.AOSCRAPMASTERID_FLD].ToString().Trim());
				}

				return objObject;					
			}
			catch(OleDbException ex)
			{			
				throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME,ex);
			}			

			catch (Exception ex) 
			{
				throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
			}

			finally 
			{
				if (oconPCS!=null) 
				{
					if (oconPCS.State != ConnectionState.Closed) 
					{
						oconPCS.Close();
					}
				}
			}
		}
		
		
		///    <summary>
		///       This method uses to get all data from PRO_AOScrapDetail
		///    </summary>
		///    <Inputs>
		///        PRO_AOScrapDetailVO       
		///    </Inputs>
		///    <Returns>
		///       void
		///    </Returns>
		///    <History>
		///       Thursday, June 09, 2005
		///    </History>
		public DataSet List()
		{
			const string METHOD_NAME = THIS + ".List()";
			DataSet dstPCS = new DataSet();		

			OleDbConnection oconPCS = null;
			OleDbCommand ocmdPCS = null;
			try 
			{
				string strSql = "SELECT "
					+ PRO_AOScrapDetailTable.AOSCRAPDETAILID_FLD + ", "
					+ PRO_AOScrapDetailTable.SCRAPQUANTITY_FLD + ", "
					+ PRO_AOScrapDetailTable.WORKORDERDETAILID_FLD + ", "
					+ PRO_AOScrapDetailTable.PRODUCTID_FLD + ", "
					+ PRO_AOScrapDetailTable.WORKORDERMASTERID_FLD + ", "
					+ PRO_AOScrapDetailTable.LINE_FLD + ", "
					+ PRO_AOScrapDetailTable.SCRAPREASONID_FLD + ", "
					+ PRO_AOScrapDetailTable.WOROUTINGID_FLD + ", "
					+ PRO_AOScrapDetailTable.STOCKUMID_FLD + ", "
					+ PRO_AOScrapDetailTable.AOSCRAPMASTERID_FLD
					+ " FROM " + PRO_AOScrapDetailTable.TABLE_NAME;

				
				oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
				ocmdPCS = new OleDbCommand(strSql, oconPCS);
				ocmdPCS.Connection.Open();
				
				OleDbDataAdapter odadPCS = new OleDbDataAdapter(ocmdPCS);
				odadPCS.Fill(dstPCS,PRO_AOScrapDetailTable.TABLE_NAME);

				return dstPCS;
			}
			catch(OleDbException ex)
			{
				throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME,ex);
			}			
			catch (Exception ex) 
			{
				throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
			}
			finally 
			{
				if (oconPCS!=null) 
				{
					if (oconPCS.State != ConnectionState.Closed) 
					{
						oconPCS.Close();
					}
					oconPCS = null;
				}
			}
		}
		
		/// <summary>
		/// Get total scrap quantity of a specific workorder item
		/// </summary>
		/// <param name="pintWorkOrderMasterID">WO id</param>
		/// <param name="pintWorkOrderDetailID">WO item id</param>
		/// <returns>decimal</returns>
		/// <author> Tuan TQ </author>
		/// <created date> 20 June 2005</created>
		public decimal GetScrapQuantity(int pintWorkOrderMasterID,  int pintWorkOrderDetailID)
		{
			const string METHOD_NAME = THIS + ".GetScrapQuantity()";
					
			OleDbConnection oconPCS = null;
			OleDbCommand ocmdPCS = null;

			try 
			{
				string strSql = "SELECT ISNULL(SUM(" + PRO_AOScrapDetailTable.SCRAPQUANTITY_FLD + "), 0) AS TotalScrapQuantity "
						+ " FROM " +   PRO_AOScrapDetailTable.TABLE_NAME 
						+ " WHERE " + PRO_AOScrapDetailTable.WORKORDERMASTERID_FLD + "=" + pintWorkOrderMasterID
						+ " AND " + PRO_AOScrapDetailTable.WORKORDERDETAILID_FLD + "=" + pintWorkOrderDetailID;
				
				
				oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
				ocmdPCS = new OleDbCommand(strSql, oconPCS);
				ocmdPCS.Connection.Open();
				
				return decimal.Parse(ocmdPCS.ExecuteScalar().ToString());
			}		
			catch(OleDbException ex)
			{
				throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME,ex);
			}			
			catch (Exception ex) 
			{
				throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
			}
			finally 
			{
				if (oconPCS!=null) 
				{
					if (oconPCS.State != ConnectionState.Closed) 
					{
						oconPCS.Close();
					}					
				}				
			}
		}

		
		/// <summary>
		/// Get detail information about AOScrapDetail of a specific AOScrap
		/// </summary>
		/// <param name="pintMasterId"></param>
		/// <returns></returns>
		public DataTable GetAOScrapDetailByMaster(int pintMasterId)
		{
			const string METHOD_NAME = THIS + ".GetAOScrapDetailByMaster()";
			DataSet dstAOScrapDetail = new DataSet();		

			OleDbConnection oconPCS = null;
			OleDbCommand ocmdPCS = null;
			try 
			{
				string strSql = "SELECT "
					+ PRO_AOScrapDetailTable.TABLE_NAME + "." + PRO_AOScrapDetailTable.AOSCRAPDETAILID_FLD + ", "
					+ PRO_AOScrapDetailTable.TABLE_NAME + "." + PRO_AOScrapDetailTable.LINE_FLD + ", "
					+ PRO_WorkOrderMasterTable.TABLE_NAME + "." +  PRO_WorkOrderMasterTable.WORKORDERNO_FLD + ", "
					+ PRO_WorkOrderDetailTable.TABLE_NAME + "." + PRO_WorkOrderDetailTable.LINE_FLD + " AS " + PRO_WorkOrderDetailTable.TABLE_NAME + PRO_WorkOrderDetailTable.LINE_FLD + ", "
					+ PRO_WorkOrderDetailTable.TABLE_NAME + "." + PRO_WorkOrderDetailTable.STATUS_FLD + " AS " + PRO_WorkOrderDetailTable.TABLE_NAME + PRO_WorkOrderDetailTable.STATUS_FLD + ", "
					+ PRO_WORoutingTable.TABLE_NAME + "." + PRO_WORoutingTable.STEP_FLD + " AS " + PRO_WORoutingTable.TABLE_NAME + PRO_WORoutingTable.STEP_FLD + ", "
					+ PRO_AOScrapDetailTable.TABLE_NAME + "." + PRO_AOScrapDetailTable.PRODUCTID_FLD + ", "
					+ ITM_ProductTable.TABLE_NAME + "." + ITM_ProductTable.CODE_FLD + " AS " + ITM_ProductTable.TABLE_NAME + ITM_ProductTable.CODE_FLD + ", "					
					+ MST_UnitOfMeasureTable.TABLE_NAME + "." + MST_UnitOfMeasureTable.CODE_FLD + " AS " + MST_UnitOfMeasureTable.TABLE_NAME +  MST_UnitOfMeasureTable.CODE_FLD + ", "
					+ ITM_ProductTable.TABLE_NAME + "." + ITM_ProductTable.DESCRIPTION_FLD + " AS " + ITM_ProductTable.TABLE_NAME + ITM_ProductTable.DESCRIPTION_FLD + ", "
					+ ITM_ProductTable.TABLE_NAME + "." + ITM_ProductTable.REVISION_FLD + " AS " + ITM_ProductTable.TABLE_NAME + ITM_ProductTable.REVISION_FLD + ", "
					+ ITM_ProductTable.TABLE_NAME + "." + ITM_ProductTable.COSTMETHOD_FLD + " AS " + ITM_ProductTable.TABLE_NAME + ITM_ProductTable.COSTMETHOD_FLD + ", "
					+ PRO_ScrapReasonTable.TABLE_NAME + "." + PRO_ScrapReasonTable.SCRAPREASONDESC_FLD + ", "
					+ PRO_AOScrapDetailTable.TABLE_NAME + "." + PRO_AOScrapDetailTable.SCRAPQUANTITY_FLD + ", "
					+ PRO_AOScrapDetailTable.TABLE_NAME + "." + PRO_AOScrapDetailTable.WORKORDERDETAILID_FLD + ", "
					+ PRO_AOScrapDetailTable.TABLE_NAME + "." + PRO_AOScrapDetailTable.WORKORDERMASTERID_FLD + ", "
					+ PRO_AOScrapDetailTable.TABLE_NAME + "." + PRO_AOScrapDetailTable.WOROUTINGID_FLD + ", "
					+ PRO_AOScrapDetailTable.TABLE_NAME + "." + PRO_AOScrapDetailTable.AOSCRAPMASTERID_FLD + ", "
					+ PRO_AOScrapDetailTable.TABLE_NAME + "." + PRO_AOScrapDetailTable.STOCKUMID_FLD + ", "
					+ PRO_AOScrapDetailTable.TABLE_NAME + "." + PRO_AOScrapDetailTable.SCRAPREASONID_FLD + ", "										
					
					+ " Convert(decimal, 0) AS " + AVAILABLE_QUANTITY_FIELD + ", "					
					+ " Convert(decimal, 0) AS " + UNIT_COST_FIELD + ", "
					+ " Convert(decimal, 0) AS " + SCRAP_COST_FIELD

					+ " FROM " + PRO_AOScrapDetailTable.TABLE_NAME + " "
					+ " LEFT JOIN " + PRO_ScrapReasonTable.TABLE_NAME  + " ON "  + PRO_AOScrapDetailTable.TABLE_NAME + "." + PRO_AOScrapDetailTable.SCRAPREASONID_FLD + "=" + PRO_ScrapReasonTable.TABLE_NAME + "." + PRO_ScrapReasonTable.SCRAPREASONID_FLD 
					+ " LEFT JOIN " + PRO_WorkOrderMasterTable.TABLE_NAME  + " ON " + PRO_AOScrapDetailTable.TABLE_NAME + "." + PRO_AOScrapDetailTable.WORKORDERMASTERID_FLD + "=" + PRO_WorkOrderMasterTable.TABLE_NAME + "." + PRO_WorkOrderMasterTable.WORKORDERMASTERID_FLD 
					+ " LEFT JOIN " + PRO_WORoutingTable.TABLE_NAME  + " ON " + PRO_AOScrapDetailTable.TABLE_NAME + "." + PRO_AOScrapDetailTable.WOROUTINGID_FLD + "=" + PRO_WORoutingTable.TABLE_NAME + "." + PRO_WORoutingTable.WOROUTINGID_FLD 
					+ " LEFT JOIN " + ITM_ProductTable.TABLE_NAME + " ON "  + PRO_AOScrapDetailTable.TABLE_NAME + "." + PRO_AOScrapDetailTable.PRODUCTID_FLD + "=" + ITM_ProductTable.TABLE_NAME + "." + ITM_ProductTable.PRODUCTID_FLD        
					+ " LEFT JOIN " + MST_UnitOfMeasureTable.TABLE_NAME + " ON "  + PRO_AOScrapDetailTable.TABLE_NAME + "." + PRO_AOScrapDetailTable.STOCKUMID_FLD + "=" + MST_UnitOfMeasureTable.TABLE_NAME + "." + MST_UnitOfMeasureTable.UNITOFMEASUREID_FLD
					+ " LEFT JOIN " + PRO_WorkOrderDetailTable.TABLE_NAME  + " ON " + PRO_AOScrapDetailTable.TABLE_NAME + "." + PRO_AOScrapDetailTable.WORKORDERDETAILID_FLD + "=" + PRO_WorkOrderDetailTable.TABLE_NAME + "." + PRO_WorkOrderDetailTable.WORKORDERDETAILID_FLD 					
					+ " WHERE " + PRO_AOScrapDetailTable.TABLE_NAME + "." + PRO_AOScrapDetailTable.AOSCRAPMASTERID_FLD + "=" + pintMasterId;

				
				oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
				ocmdPCS = new OleDbCommand(strSql, oconPCS);
				ocmdPCS.Connection.Open();
				
				OleDbDataAdapter odadPCS = new OleDbDataAdapter(ocmdPCS);
				odadPCS.Fill(dstAOScrapDetail, PRO_AOScrapDetailTable.TABLE_NAME);

				return dstAOScrapDetail.Tables[PRO_AOScrapDetailTable.TABLE_NAME];
			}
			catch(OleDbException ex)
			{
				throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME,ex);
			}			
			catch (Exception ex) 
			{
				throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
			}
			finally 
			{
				if (oconPCS!=null) 
				{
					if (oconPCS.State != ConnectionState.Closed) 
					{
						oconPCS.Close();
					}
					oconPCS = null;
				}
			}
		}
		
		#endregion Retrieve Data Methods
	}
}